package com.ruoyi.iot.influxdb.repository.metric;

import com.ruoyi.iot.influxdb.query.DeviceLogQuery;
import org.influxdb.annotation.Measurement;
import org.springframework.data.domain.Pageable;

import java.text.MessageFormat;


/**
 * Created by Andy.Yang on 2019-05-28.
 */
public class InfluxdbSqlBuilder {

    private static final String Detail_COUNT_SQL_NOZONE = "SELECT COUNT({0}) FROM( SELECT {1} FROM {2} WHERE deviceId = ''{3}'' AND time >= ''{4,date,yyyy-MM-dd HH:mm:ss}'' AND time < ''{5,date,yyyy-MM-dd HH:mm:ss}'' )";

    private static final String Detail_COUNT_SQL = "SELECT COUNT({0}) FROM( SELECT {1} FROM {2} WHERE deviceId = ''{3}'' AND time >= ''{4,date,yyyy-MM-dd HH:mm:ss}'' AND time < ''{5,date,yyyy-MM-dd HH:mm:ss}'' TZ(''Asia/Shanghai'') )";

    private static final String Detail_SELECT_SQL = "SELECT {0} FROM {1} WHERE deviceId = ''{2}'' AND time >= ''{3,date,yyyy-MM-dd HH:mm:ss}'' AND time < ''{4,date,yyyy-MM-dd HH:mm:ss}'' {5} LIMIT {6,number,#} OFFSET {7,number,#} TZ(''Asia/Shanghai'')";

    private static final String TimeInterval_COUNT_SQL = "SELECT COUNT({0}) FROM( SELECT {1} FROM {2} WHERE deviceId = ''{3}'' AND time >= ''{4,date,yyyy-MM-dd HH:mm:ss}'' AND time < ''{5,date,yyyy-MM-dd HH:mm:ss}'' GROUP BY time({6}) TZ(''Asia/Shanghai'') )";

    private static final String TimeInterval_SELECT_SQL = "SELECT {0} FROM {1} WHERE deviceId = ''{2}'' AND time >= ''{3,date,yyyy-MM-dd HH:mm:ss}'' AND time < ''{4,date,yyyy-MM-dd HH:mm:ss}'' GROUP BY time({5}) {6} LIMIT {7,number,#} OFFSET {8,number,#} TZ(''Asia/Shanghai'')";

    private static final String Detail_COUNT_SQL_Device_NOZONE = "SELECT COUNT({0}) FROM( SELECT {1} FROM {2} WHERE deviceId = ''{3}'' AND time >= ''{4,date,yyyy-MM-dd HH:mm:ss}'' AND time < ''{5,date,yyyy-MM-dd HH:mm:ss}'' )";

    private static final String Detail_COUNT_SQL_Device = "SELECT COUNT({0}) FROM( SELECT {1} FROM {2} WHERE deviceId = ''{3}'' AND time >= ''{4,date,yyyy-MM-dd HH:mm:ss}'' AND time < ''{5,date,yyyy-MM-dd HH:mm:ss}'' TZ(''Asia/Shanghai'') )";

    private static final String Detail_COUNT_SQL_PositionNum = "SELECT COUNT({0}) FROM( SELECT {1} FROM {2} WHERE deviceId = ''{3}'' and sortNum=''{4}'' AND time >= ''{5,date,yyyy-MM-dd HH:mm:ss}''+8h AND time < ''{6,date,yyyy-MM-dd HH:mm:ss}'' +8h TZ(''Asia/Shanghai'') )";

    /**
     * 获取指标对象表名称
     * @param clazz
     * @return 指标对象表名称
     */
    public static String findMeasurementName(Class<?> clazz) {
        return ((Measurement)clazz.getAnnotation(Measurement.class)).name();
    }

    /**
     * 构建总条数SQL，明细数据
     * @param query
     * @return
     */
    public static String buildCountSqlWithDetailNOZONE( DeviceLogQuery query ) {
        String countSql = MessageFormat.format(Detail_COUNT_SQL_NOZONE,
                query.getCountColumn(),
                query.getSelectColumns(),
                query.getTableName(),
                query.getDeviceId(),
                query.getStartDate(),
                query.getEndDate() );
        return countSql;
    }

    /**
     * 构建总条数SQL，明细数据
     * @param query
     * @return
     */
    public static String buildCountSqlWithDetailNoZone( DeviceLogQuery query ) {
        String countSql = MessageFormat.format(Detail_COUNT_SQL_NOZONE,
                query.getCountColumn(),
                query.getSelectColumns(),
                query.getTableName(),
                query.getDeviceId(),
                query.getStartDate(),
                query.getEndDate() );
        return countSql;
    }

    /**
     * 构建总条数SQL，明细数据
     * @param query
     * @return
     */
    public static String buildCountSqlWithDetail( DeviceLogQuery query ) {
        String countSql = MessageFormat.format(Detail_COUNT_SQL,
                query.getCountColumn(),
                query.getSelectColumns(),
                query.getTableName(),
                query.getDeviceId(),
                query.getStartDate(),
                query.getEndDate() );
        return countSql;
    }

    /**
     * 构建查询SQL，明细数据
     * @param query
     * @param page
     * @return
     */
    public static String buildSelectSqlWithDetail( DeviceLogQuery query, Pageable page ) {
        // 排序字段
        String sortStr = "";
        if ( page.getSort().isSorted() ){
            sortStr = "ORDER BY " + page.getSort().toString().replace( ':', ' ' );
        }

        String selectSql = MessageFormat.format(Detail_SELECT_SQL,
                query.getSelectColumns(),
                query.getTableName(),
                query.getDeviceId(),
                query.getStartDate(),
                query.getEndDate(),
                sortStr,
                page.getPageSize(),
                page.getPageNumber() * page.getPageSize()
        );
        return selectSql;
    }

    /**
     * 构建总条数SQL 带时间分组
     * @param query
     * @return
     */
    public static String buildCountSqlWithTimeInterval( DeviceLogQuery query ) {
        String countSql = MessageFormat.format(TimeInterval_COUNT_SQL,
                query.getCountColumn(),
                query.getSelectColumns(),
                query.getTableName(),
                query.getDeviceId(),
                query.getStartDate(),
                query.getEndDate(),
                query.getTimeInterval() );
        return countSql;
    }


    /**
     * 构建查询SQL 带时间分组
     * @param query
     * @param page
     * @return
     */
    public static String buildSelectSqlWithTimeInterval( DeviceLogQuery query, Pageable page ) {
        // 排序字段
        String sortStr = "";
        if ( page.getSort().isSorted() ){
            sortStr = "ORDER BY " + page.getSort().toString().replace( ':', ' ' );
        }

        String selectSql = MessageFormat.format(TimeInterval_SELECT_SQL,
                query.getSelectColumns(),
                query.getTableName(),
                query.getDeviceId(),
                query.getStartDate(),
                query.getEndDate(),
                query.getTimeInterval(),
                sortStr,
                page.getPageSize(),
                page.getPageNumber() * page.getPageSize()
        );
        return selectSql;
    }

    /**
     * 构建总条数SQL，明细数据 根据设备编号获取
     * @param query
     * @return
     */
    public static String buildCountSqlWithDetailByDeviceIdNOZONE( DeviceLogQuery query ) {
        String countSql = MessageFormat.format(Detail_COUNT_SQL_Device_NOZONE,
                query.getCountColumn(),
                query.getSelectColumns(),
                query.getTableName(),
                query.getDeviceId(),
                query.getStartDate(),
                query.getEndDate() );
        return countSql;
    }

    /**
     * 构建总条数SQL，明细数据 根据设备编号获取
     * @param query
     * @return
     */
    public static String buildCountSqlWithDetailByDeviceId( DeviceLogQuery query ) {
        String countSql = MessageFormat.format(Detail_COUNT_SQL_Device,
                query.getCountColumn(),
                query.getSelectColumns(),
                query.getTableName(),
                query.getDeviceId(),
                query.getStartDate(),
                query.getEndDate() );
        return countSql;
    }



}


